import os
import copy
import httpx
from asyncio import sleep
from datetime import datetime
from pydantic import BaseModel, Field
from typing import Optional, Annotated
from datetime import date
from typing import Literal
import uuid
import pandas as pd


# 获取Excel全部sheet name
async def get_excel_sheet_names(
        file_path: Annotated[str, Field(
            description="Excel文件路径，如：http://abc.com/files/42fsfsffddfs/ai.txt,/Users/thtf/Desktop/ask_for_leave.py")],
) -> list:
    result = {"sheet_names": [], "sheet_data": {}}

    with pd.ExcelFile(file_path) as excel_file:
        # 获取所有Sheet名称
        result["sheet_names"] = excel_file.sheet_names

        # 遍历每个Sheet，读取前N条数据
        for sheet_name in excel_file.sheet_names:
            df = pd.read_excel(excel_file, sheet_name=sheet_name, nrows=3)
            result["sheet_data"][sheet_name] = df.to_dict(orient="records")  # 转为字典列表

    return result


# 获取表头信息
async def get_excel_header(
        file_path: Annotated[str, Field(
            description="Excel文件路径，如：http://abc.com/files/42fsfsffddfs/ai.txt,/Users/thtf/Desktop/ask_for_leave.py")],
        sheet_name: Annotated[str, Field(description="要分析的Excel Sheet名称，如：Sheet1")],
) -> list:
    # file_path = "http://10.10.93.198:8090/files/2099b79b-dc3b-47b3-b6c4-7bc83fc32649/file-preview?timestamp=1748937687&nonce=8fd8b8c892b0854c08e6c15e3e17bbba&sign=Q2b7KyFOuOgU8krIBAmcGyZnuhtCEZ4fYMtasgRMDBI="
    df = pd.read_excel(file_path, sheet_name="Sheet1")
    return list(df.columns)


# 执行表格分析语句
async def excel_analyse(
        file_path: Annotated[str, Field(
            description="Excel文件路径，如：http://abc.com/files/42fsfsffddfs/ai.txt,/Users/thtf/Desktop/ask_for_leave.py")],
        sheet_name: Annotated[str, Field(description="要分析的Excel Sheet名称，如：Sheet1")],
        analyes_code: Annotated[str, Field(
            description='''
            用于Excel分析的python代码(注意代码的换行，避免使用\n做为换行符)，代码的最后必须要使用result变量做为执行结果。如：
            import pandas as pd;
            df = pd.read_excel("{file_path}", sheet_name="{sheet_name}");
            dept_counts = df['所属部门'].value_counts();
            result = dept_counts;
            '''
        )],
) -> str:
    # file_path = "http://10.10.93.198:8090/files/2099b79b-dc3b-47b3-b6c4-7bc83fc32649/file-preview?timestamp=1748937687&nonce=8fd8b8c892b0854c08e6c15e3e17bbba&sign=Q2b7KyFOuOgU8krIBAmcGyZnuhtCEZ4fYMtasgRMDBI="
    new_code = analyes_code.format(file_path=file_path, sheet_name=sheet_name)
    new_code.replace('\n', ';')
    # code = bytes(new_code, 'utf-8').decode('unicode_escape')
    # print(code)
    local_vars = {}
    # print(new_code)
    exec(new_code, local_vars)

    # print()
    return (
        local_vars.get("result")
    )

    # if __name__ == "__main__":
    import asyncio
#     acode = '''
# import pandas as pd
#
# # 读取Excel文件
# df = pd.read_excel("{file_path}", sheet_name="{sheet_name}")
#
# # 统计各部门人数
# dept_counts = df['所属部门'].value_counts()
#
# # 输出统计结果
# result = dept_counts
#     '''
#     acode = '''
# import pandas as pd\n# 加载数据\ndata = df\n# 计算每个部门的员工数\ndepartment_counts = data['所属部门'].value_counts()\n# 将结果转换为字符串形式以便输出\nresult = department_counts.to_string()
#     '''
#     asyncio.run(excel_analyse(
#         sheet_name="Sheet1",
#         file_path="src/files/员工信息表.xlsx",
#         analyes_code=acode,
#     ))
